package hibernate.hibernateInterface;

import java.util.Date;
import java.util.List;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Expression;

import hibernate.p.Address;
import hibernate.p.DocType;

import hibernate.billStream.Bill;
import hibernate.clientAccount.*;
import hibernate.clientAccount.ClientVIPInfo.Level;
import hibernate.roomSystem.*;

public class ClientSystemHibernateInterface {

	public static List getClientBasicInfoByRegTime(Date time,int compareCond,Session session)throws Exception{
		String sqlScript = "from ClientBasicInfo info where info.registerDate " + CompareCond.parseCompareScript(compareCond) + "?";
		Query query=session.createQuery(sqlScript).setDate(0,time);
		List list=query.list();
		return list;
	}
	
	public static AbstractClient getClientByID(Long clientID,Class clientClass,Session session)throws Exception
	{
		AbstractClient client;
		Criteria criteria = session.createCriteria(SingleClient.class);
		Criterion criterion = Expression.eq("id",clientID);
		criteria = criteria.add(criterion);
		List resultList = criteria.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("ClientOverlappingException");
		if (clientClass.equals(SingleClient.class))
			client = (SingleClient)resultList.get(0);
		else
			client = (Group)resultList.get(0);
		return client;
	}
	
	public static List getSingleClientsByName(String firstName,String middleName,String lastName,String matchCode,Session session)throws Exception
	{
		String sqlScript = "from SingleClient sc,ClientBasicInfo cbi where sc.id = cbi.id and ";
		String firstNameScript = "";
		String middleNameScript = "";
		String lastNameScript = "";
		if (matchCode.charAt(0) == '1')
		{
			if (Integer.parseInt(matchCode) != 100)
				firstNameScript = "cbi.firstName = '" + firstName + "' and ";
			else
				firstNameScript = "cbi.first = '" + firstName+"'";
		}
		if (matchCode.charAt(1) == '1')
		{
			if (matchCode.charAt(2) == '1')
				middleNameScript = "cbi.middleName = '" + middleName + "' and ";
			else
				middleNameScript = "cbi.middleName = '" + middleName+"'";
		}
		if (matchCode.charAt(2) == '1')
			lastNameScript = "cbi.lastName = '" + lastName+"'";
		sqlScript += firstNameScript + middleNameScript + lastNameScript;
		Query query = session.createQuery(sqlScript);
		List resultList = query.list();
		return resultList;
	}
	
	public static List getSingleClientsByAge(int age,int compareScript,Session session)throws Exception
	{
		String queryScript = "from SingleClient sc,ClientBasicInfo cbi where sc.id = cbi.id and cbi.age" + CompareCond.parseCompareScript(compareScript) + age;
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		return resultList;
	}
	
	public static List getSingleClientsByBirthday(Date birthday,Session session)throws Exception
	{
		String queryScript = "from SingleClient sc,ClientBasicInfo cbi where sc.id = cbi.id and cbi.birthday = ?";
		Query query = session.createQuery(queryScript).setDate(0, birthday);
		List resultList = query.list();
		return resultList;
	}
	
	public static SingleClient getSingleClientByDocNumber(String docNum,Session session)throws Exception
	{
		String queryScript = "from SingleClient sc,ClientBasicInfo cbi where sc.id = cbi.id and cbi.docType = '" + docNum+"'";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("ClientOverlappingException");
		SingleClient client = (SingleClient)resultList.get(0);
		return client;
	}
	
	public static List getSingleClientsByDocType(DocType docType,Session session)throws Exception
	{
		String queryScript = "from SingleClient sc,ClientBasicInfo cbi where sc.id = cbi.id and cbi.docType = '" + docType.toString()+"'";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		return resultList;
	}
	
	public static SingleClient getSingleClientByAddresss(Address address,Session session)throws Exception
	{
		String queryScript = "from SingleClient sc,ClientBasicInfo cbi where sc.id = cbi.id and cbi.address = '" + address.toString()+"'";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("ClientOverlappingException");
		SingleClient client = (SingleClient)resultList.get(0);
		return client;
	}
	
	public static List getSingleClientsInHotel (Session session)throws Exception
	{
		String queryScript = "from SingleClient sc where sc.isCurrentInHotel = true";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		return resultList;
	}
	
	public static Set getSingleClientsInGroup(Long groupID,Session session)throws Exception
	{
		Criteria criteria = session.createCriteria(Group.class);
		Criterion criterion = Expression.eq("id",groupID);
		criteria = criteria.add(criterion);
		List resultList = criteria.list();
		if (resultList.size() == 0)
			throw new Exception("GroupNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("GroupOverlappingException");
		Group group = (Group)resultList.get(0);
		return group.getCustomerList();
	}
	
	/*
	public static SingleClient getSingleClientByBill (Long billID,Session session)throws Exception
	{
		Deposite payer=((Bill)session.get(Bill.class,billID)).getDepositePayer();
		
		String queryScript = "from SingleClient sc,ConcreteBill cb where sc.id = cb.id and cb.id = " + billID;
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("ClientOverlappingException");
		SingleClient client = (SingleClient)resultList.get(0);
		return client;
	}*/
	
	public static VIPClient getVIPByUsername(String username,Session session)throws Exception
	{
		String queryScript = "from VIPClient vc,ClientVIPInfo cvi where vc.id = cvi.id and cvi.username = '" + username+"'";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("ClientOverlappingException");
		VIPClient client = (VIPClient)resultList.get(0);
		return client;
	}
	
	public static List getVIPByLevel(Level vipLevel,Session session)throws Exception
	{
		String queryScript = "from VIPClient vc,ClientVIPInfo cvi where vc.id = cvi.id and cvi.vipLevel = " + vipLevel.toString();
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		return resultList;
	}
	
	public static List getClientsByCondition(String sqlScript,Session session)throws Exception
	{
		Query query = session.createQuery(sqlScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("ClientNotFoundException");
		return resultList;
	}
	
	public static List getAllVIPS(Session session)throws Exception
	{
		String queryScript = "from VIPClient vc";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		return resultList;
	}
	
	public static List getAllSingleClients(Session session)throws Exception
	{
		String queryScript = "from SingleClient sc";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		return resultList;
	}
	
	public static List getAllIndependentGroups(Session session)throws Exception
	{
		String queryScript = "from Group g";
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		for (int i = 0;i < resultList.size();i++)
			if (((Group)resultList.get(i)).getGroup() != null)
				resultList.remove(i);
		return resultList;
	}
}
